package db1;

import beans1.Officebean;
import lombok.Getter;
import lombok.Setter;

import java.sql.*;
import java.util.ArrayList;


public class OfficeDAO extends ConnBean{
    private Connection connection=null;
    public ArrayList<Officebean> getAllRecords(){
        ResultSet rs=null;
        Statement stmt=null;
        ArrayList<Officebean> list=new ArrayList<Officebean>();
        try {
            //获取数据库连接(url,username,password)
            connection = getConnection();
            if(connection==null)
                System.out.println("error");
            //准备执行数据库查询
            String sql="select * from YY_KS";
            //准备SQL执行;
            stmt =connection.createStatement();
            //执行数据库查询，获取结果集
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                Officebean user=new Officebean();
                user.setID(rs.getString("id"));
                user.setNAME(rs.getString("name"));
                list.add(user);
            }
            //关闭表达式对象
            stmt.close();
            //关闭数据库
            connection.close();

        }catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try{
                if (connection!=null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        return list;
    }


    public boolean deleteRecord(Officebean user) {
        PreparedStatement pstmt = null;
        String sql = "delete from YY_KS where ID=?";

        if (user == null)
            return false;
        int result = 0;
        try {
            connection = getConnection();
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, user.getID());
            result = pstmt.executeUpdate();
            //关闭表达式对象
            pstmt.close();
            //关闭数据库
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null && (!connection.isClosed())) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (result > 0)
            return true;
        else
            return false;
    }
    public boolean editOffice(Officebean user) throws SQLException {
        connection = getConnection();
        String sql="update YY_KS set name=? where id=?";

        PreparedStatement pstmt=connection.prepareStatement(sql);
        //设置占位符值
        pstmt.setString(1, user.getNAME());
        pstmt.setString(2, user.getID());
        int res=pstmt.executeUpdate();
        if(res>0){
            System.out.println("更新数据成功");
        }
        pstmt.close();
        connection.close();
        return true;
    }

    public boolean insertOffice(Officebean user) throws SQLException {
        connection = getConnection();
//        String sql="update YY_PATIENT set password=?,name=?,idNumber=?,sex=?,birthDate=?,TELNUMBER=? where account=?";
        //预置对象YY_PATIENT
        String sql = "INSERT INTO YY_KS(ID,NAME) VALUES(?,?)";
        PreparedStatement pstmt=connection.prepareStatement(sql);
        pstmt.setString(1, user.getID());
        pstmt.setString(2, user.getNAME());
        int res=pstmt.executeUpdate();
        if(res>0){
            System.out.println("成功加入数据");
        }
        pstmt.close();
        connection.close();
        return true;
    }
}
